package com.ruoyi.common.core.excel;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

public class ExcelReader {

    private Workbook workbook;
//    private PrintWriter writer;

    // 文件map，key=sheet页的名称，value=sheetMap
    private Map<String, Map<String, String>> fileMap = null;

    // sheetMap, key=坐标值，value=坐标对应的值
    private Map<String, String> sheetMap = null;

    public ExcelReader(String filePath) throws IOException {
        fileMap = new HashMap<>();
        FileInputStream excelFile = new FileInputStream(new File(filePath));
        this.workbook = new XSSFWorkbook(excelFile);
//        this.writer = new PrintWriter(new File(outputFilePath));
    }

    // 读取所有sheet页
    public Map<String, Map<String, String>> readAllSheets() throws IOException {
        for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet datatypeSheet = workbook.getSheetAt(i);
//            writer.println("Sheet name: " + datatypeSheet.getSheetName());
//            readSheet(datatypeSheet);
//            System.out.println(datatypeSheet.getSheetName());
            fileMap.put(datatypeSheet.getSheetName(), readSheet(datatypeSheet));
        }
        workbook.close();
//        writer.close();
        return fileMap;
    }

    // 读取单个sheet页
    private Map<String, String> readSheet(Sheet datatypeSheet) {
        Iterator<Row> iterator = datatypeSheet.iterator();
        sheetMap = new HashMap<>();
        String sheetMapKey = "";
        String sheetMapValue = "";
        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Iterator<Cell> cellIterator = currentRow.iterator();

            while (cellIterator.hasNext()) {

                Cell currentCell = cellIterator.next();
                // 打印行列坐标
//                writer.print("Coordinate: (" + (currentRow.getRowNum() + 1) + ", " + (currentCell.getColumnIndex() + 1) + ") - ");
                sheetMapKey = "" + (currentRow.getRowNum() + 1) + "," + (currentCell.getColumnIndex() + 1) + "";
                // 如果单元格类型是合并单元格，则使用特殊处理
                if(isMergedRegion(datatypeSheet, currentRow.getRowNum(), currentCell.getColumnIndex())) {
//                    writer.println(getMergedRegionValue(datatypeSheet, currentRow.getRowNum(), currentCell.getColumnIndex()));
                    sheetMapValue = getMergedRegionValue(datatypeSheet, currentRow.getRowNum(), currentCell.getColumnIndex());
                } else {
                    // 根据不同的数据类型处理
                    currentCell.setCellType(CellType.STRING);
                    sheetMapValue = currentCell.getStringCellValue();
//                    CellType type = CellType.STRING;
//                    if (1 == CellType.STRING.getCode()) {
////                        writer.println(currentCell.getStringCellValue());
//                        sheetMapValue = currentCell.getStringCellValue();
//                    } else if (0 == CellType.NUMERIC.getCode()) {
////                        writer.println(currentCell.getNumericCellValue());
//                        sheetMapValue = String.valueOf(currentCell.getNumericCellValue());
//                    }else {
//                        sheetMapValue = "";
//                    }
                }
                sheetMap.put(sheetMapKey, sheetMapValue);
            }
        }
        return sheetMap;
    }

    // 检查给定的单元格是否在合并区域内
    private boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(row, column)) {
                return true;
            }
        }
        return false;
    }

    // 如果单元格在合并区域内，获取合并区域的值
    private String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            if (range.isInRange(row, column)) {
                Row r = sheet.getRow(range.getFirstRow());
                Cell c = r.getCell(range.getFirstColumn());
                return c.getStringCellValue();
            }
        }
        return null;
    }

    public static void main(String[] args) {
        try {
            ExcelReader reader = new ExcelReader("D:\\菜鸟无忧物流运费.xlsx");
            Map<String, Map<String, String>> fileMap = reader.readAllSheets();
            System.out.println("读取完成");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
